package com.fastCms.business.bim.plugins.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Wenbo
 * @date 2021/12/7 上午10:08
 */
public class ExcelImport {

    /**
     * 导入
     *
     * @param file
     * @return
     */
    public static List<List<String>> importFromExcel(File file) {
        // 用于保存读取的Excel信息
        List<List<String>> excelListList = new ArrayList<List<String>>();
        // 创建工作簿
        Workbook workBook = null;
        // 获取文件名
        String fileName = file.getName();
        // 判断Excel类型，是Excel2003还是Excel2007，通过文件名后缀判断
        try {
            if (fileName.endsWith("xls")) {
                workBook = new HSSFWorkbook(new FileInputStream(file));
            } else if (fileName.endsWith("xlsx")) {
                workBook = new XSSFWorkbook(new FileInputStream(file));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 获得第一个sheet
        Sheet sheet = workBook.getSheetAt(0);
        // 获得该sheet的所有行
        int rows = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < rows; i++) {
            // 获取一行
            Row row = sheet.getRow(i);
            // 第一行为标题行，跳过
            if (i == 0) {
                continue;
            }

            // 获得列数
            int cellNums = row.getLastCellNum();
            // 用于保存每行数据
            List<String> excelList = new ArrayList<String>();
            // 一次保存列信息
            for (int j = 0; j < cellNums; j++) {
                Cell firstCell = row.getCell(0);
                if (getCellValue(firstCell) == null || "".equals(getCellValue(firstCell))) {
                    break;
                }
                Cell cell = row.getCell(j);
                String cellValue = getCellValue(cell).trim();
                excelList.add(cellValue);
            }
            if (excelList.size() > 0) {
                excelListList.add(excelList);
            }
        }

        return excelListList;
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        String cellValue = null;
        if (cell == null) {
            cellValue = "";
            return cellValue;
        }
        // 获取单元格类型
        DecimalFormat decimalFormat = new DecimalFormat("0");
        switch (cell.getCellTypeEnum()) {
            case BLANK:
                cellValue = "";
                break;
            case NUMERIC:
                // 判断是否为日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = dateFormat.format(cell.getDateCellValue());
                } else {
                    String number = String.valueOf(cell.getNumericCellValue());
                    // 是否是浮点数
                    if (number.contains(".")) {
                        decimalFormat = new DecimalFormat("#.# ##");
                    }
                    cellValue = decimalFormat.format(cell.getNumericCellValue());
                }
                break;
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case FORMULA:
                // 公式需要获取其数值
                cell.setCellType(CellType.NUMERIC);
                cellValue = decimalFormat.format(cell.getNumericCellValue());
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
            default:
                cellValue = cell.getStringCellValue();
                break;
        }

        return cellValue;
    }


}
